package com.edu.dao;


import com.edu.data.Admin;
import com.edu.data.ClassInfo;
import com.edu.data.Contact;
import com.edu.data.Page;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Repository
public class DaoImpl implements Dao {

	private Connection conn = null;

	public DaoImpl() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/contract?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false"
					+ "&user=root&password=admin");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/***
	 * 增加通讯录记录（注册）
	 * @param contract
	 */
	public void addContact(Contact contract) {
		try {
			PreparedStatement pst = conn.prepareStatement("insert into contract(name, stuid, sex, classId, mobile, email,qq) values(?,?,?,?,?,?,?)");
			pst.setString(1, contract.getName());
			pst.setString(2, contract.getStuid());
			pst.setString(3, contract.getSex());
			pst.setInt(4, contract.getClassInfo().getId());
			pst.setString(5, contract.getMobile());
			pst.setString(6, contract.getEmail());
			pst.setString(7, contract.getQq());
		
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/***
	 * 根据班级id查询班级信息
	 * @param classId
	 * @return
	 */
	public ClassInfo getClassInfo(int classId) {
		ClassInfo classInfo = new ClassInfo();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from classinfo where id="+classId);
			if(rs.next()){				
				classInfo.setId(rs.getInt("id"));
				classInfo.setName(rs.getString("name"));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return classInfo;
	}

	/**
	 * 获取通讯录总记录数
	 * @return
	 */
	@Override
	public long getContactsCount() {
		long count=0;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select count(*) from contract");

			if(rs.next()){
				count=rs.getLong(1);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public static void main(String[] args){
		Dao dao = new DaoImpl();
		
		//System.out.println(dao.getClassInfo(4).getName());
//		Contract contract = new Contract();
//		contract.setName("����");
//		contract.setStuid("2012323232");
//		contract.setSex("M");
//		contract.setClassInfo(dao.getClassInfo(1));
//		
//		contract.setMobile("1334343434");
//		contract.setEmail("zhangs@qq.com");
//		contract.setQq("2202323");
//		
//		dao.addContract(contract);
		
		System.out.println(dao.login("admin", "12345").getName());
	}

	/**
	 * 查询所以通讯录
	 * @return
	 */
	public List<Contact> getAllContacts() {
		List<Contact> contacts = new ArrayList<Contact>();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from contract");
			
			while(rs.next()){				
				Contact contact=createContactResultSet(rs);
				contacts.add(contact);
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return contacts;
	}



	/***
	 * 分页查询所有通讯录记录
	 * @param page
	 * @return
	 */
	@Override
	public List<Contact> getAllContactsByPage(Page page) {
		List<Contact> contacts = new ArrayList<Contact>();
		try{
			Statement st = conn.createStatement();
			String sql="";
			if(page!=null){
				sql+="select * from contract limit "+page.getStart()+","+page.getPageSize();
			}else{
				sql="select * from contract";
			}
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				Contact contact=createContactResultSet(rs);
				contacts.add(contact);
			}
		}
		catch (Exception e){
			e.printStackTrace();
		}
		return contacts;
	}

	/***
	 * 登录
     */
	public Admin login(String account, String password) {
		try {
			PreparedStatement pst = conn.prepareStatement("select * from admin where account=? and password=?");
			pst.setString(1, account);
			pst.setString(2, password);
			
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				Admin admin = new Admin();
				
				admin.setId(rs.getInt("id"));
				admin.setAccount(account);
				admin.setPassword(password);
				admin.setName(rs.getString("name"));
				
				return admin;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/***
	 * 删除通讯录记录
	 * @param id
	 */
	public void deleteContact(int id) {
		try {
			Statement st = conn.createStatement();
			st.executeUpdate("delete from contract where id="+id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/****
	 * 根据id获取通讯录记录
	 * @param id
	 * @return
	 */
	public Contact getContact(int id) {
		Contact contact = new Contact();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select * from contract where id="+id);
			
			if(rs.next()){
				contact=createContactResultSet(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return contact;
	}

	/***
	 * 更新通讯录记录
	 * @param contact
	 */
	public void updateContact(Contact contact) {
		try {
			PreparedStatement pst = conn.prepareStatement("update contract set name=?, stuid=?, sex=?, classId=?, mobile=?, email=?,qq=? where id=?");
			pst.setString(1, contact.getName());
			pst.setString(2, contact.getStuid());
			pst.setString(3, contact.getSex());
			pst.setInt(4, contact.getClassInfo().getId());
			pst.setString(5, contact.getMobile());
			pst.setString(6, contact.getEmail());
			pst.setString(7, contact.getQq());
			pst.setInt(8, contact.getId());
			
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}

	public Contact createContactResultSet(ResultSet rs) throws SQLException {
		Contact contact=new Contact();
		contact.setId(rs.getInt("id"));
		contact.setName(rs.getString("name"));
		contact.setStuid(rs.getString("stuid"));
		contact.setSex(rs.getString("sex"));
		contact.setClassInfo(this.getClassInfo(rs.getInt("classId")));
		contact.setEmail(rs.getString("email"));
		contact.setMobile(rs.getString("mobile"));
		contact.setQq(rs.getString("qq"));
		return contact;
	}
}
